#!/bin/bash
source /etc/profile
if [ -n "$1" ] ;then
do_date=$1
else
do_date=`date -d "-1 day" +%F`
fi

sql="
set hive.execution.engine = tez;
select 
  device_id, count(1) sum 
from(
  select 
    device_id, dt, date_sub(dt, row_number() over(partition by device_id order by dt)) gid 
  from 
    dws.dws_member_start_day 
  where 
    dt between  date_sub('$do_date', -7) and '$do_date'
)t1
group by 
  device_id, gid
having 
  sum >= 3;
"

hive -e "$sql"